"""
Case Type   : HTAP行列融合特性
Case Name   : HTAP行列融合特性--二级分区表-列表分区表
Create At   : 2025/11/1
Owner       : sungang14
Description :
    1.建表
    2.插入数据
    3.全表转换
    4.主备查询
    5.取消转换
    6.部分列转换
    7.主备查询
    8.取消转换
    9.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.查询成功
    5.执行成功
    6.执行成功
    7.查询成功
    8.执行成功
    9.执行成功
History     :
"""

import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from yat.test import macro


class HTAPIntegerTypes(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.sta2_sh = CommonSH('Standby2DbUser')
        self.constant = Constant()
        self.pg_hba_backup = None
        text = '----备份pg_hba.conf文件;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'cp {macro.DB_INSTANCE_PATH}/pg_hba.conf {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)

        text = '----配置主机信任连接;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'gs_guc reload -N all -I all -h "host all all {self.pri_sh.node.db_host}/32 trust";' \
              f'gs_guc reload -N all -I all -h "host all all {self.sta1_sh.node.db_host}/32 trust";' \
              f'gs_guc reload -N all -I all -h "host all all {self.sta2_sh.node.db_host}/32 trust"'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)
        self.table_name = 't_htap_0009'

    def test_integer_types_htap(self):
        text = '----step1:建表;expect:执行成功----'
        self.log.info(text)
        sql_cmd = f'drop table if exists {self.table_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res, f'执行失败:{text}')
        sql = f"""create table {self.table_name}
                    (
                        number integer,
                        name char(20),
                        class char(20),
                        grade integer
                    )
                    partition by list(class) subpartition by list(grade)
                    (
                        partition class_01 values ('21.00')(
                            subpartition class_01_a values ( '0' ),
                            subpartition class_01_b values ( '1' )
                        ),
                        partition class_02 values ('21.01')(
                            subpartition class_02_a values ( '0' ),
                            subpartition class_02_b values ( '1' )
                        ),
                        partition class_03 values ('21.02')(
                            subpartition class_03_a values ( '0' ),
                            subpartition class_03_b values ( '1' )
                        ),
                        partition class_04 values ('21.03')(
                            subpartition class_04_a values ( '0' ),
                            subpartition class_04_b values ( '1' )
                        ),
                        partition class_05 values ('21.04')(
                            subpartition class_05_a values ( '0' ),
                            subpartition class_05_b values ( '1' )
                        ),
                        partition class_06 values ('21.05')(
                            subpartition class_06_a values ( '0' ),
                            subpartition class_06_b values ( '1' )
                        ),
                        partition class_07 values ('21.06')(
                            subpartition class_07_a values ( '0' ),
                            subpartition class_07_b values ( '1' )
                        ),
                        partition class_08 values ('21.07')(
                            subpartition class_08_a values ( '0' ),
                            subpartition class_08_b values ( '1' )
                        ),
                        partition class_09 values ('21.08')(
                            subpartition class_09_a values ( '0' ),
                            subpartition class_09_b values ( '1' )
                        )
                    );"""
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.TABLE_CREATE_SUCCESS, q_res, f'执行失败:{text}')

        text = '----step2:插入数据;expect:执行成功----'
        self.log.info(text)
        sql = f"""begin
                        for i in 1..10000 loop
                            insert into {self.table_name} values(i,'alan','21.0'||i%9,i%2);
                        end loop;
                    end;"""
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, q_res, f'执行失败:{text}')
        self.sql_select = f"select count(*) from {self.table_name} where number between 299 and 5999;"
        q_res = self.pri_sh.execut_db_sql(self.sql_select)
        self.log.info(q_res)
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step3:全表转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} imcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step4:主备查询;expect:查询成功----'
        self.log.info(text)
        q_res = self.pri_sh.execut_db_sql(self.sql_select)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        self.sql_select1 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where number between 299 and 5999;"
        q_res = self.sta1_sh.execut_db_sql(self.sql_select1)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step5:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step6:部分列转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} imcstored(number, class);"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step7:主备查询;expect:查询成功----'
        self.log.info(text)
        self.sql_select2 = f"select count(*) from {self.table_name} where class>'21.03';"
        self.sql_select3 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where class>'21.03';"
        q_res = self.pri_sh.execut_db_sql(self.sql_select2)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 5555\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select3)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 5555\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step8:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step9:指定分区转换; expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition class_02 imcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step10:主备查询;expect:查询成功----'
        self.log.info(text)
        self.sql_select4 = f"select count(*) from {self.table_name} where class='21.01';"
        self.sql_select5 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where class='21.01';"
        q_res = self.pri_sh.execut_db_sql(self.sql_select4)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 1112\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select5)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 1112\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step11:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition class_02 unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step12:指定分区的部分列转换; expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition class_02 imcstored(number,class);"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step13:主备查询;expect:查询成功----'
        self.log.info(text)
        q_res = self.pri_sh.execut_db_sql(self.sql_select4)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 1112\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select5)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 1112\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step14:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition class_02 unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

    def tearDown(self):
        text = '----step15:清理环境;expect:执行成功----'
        self.log.info(text)
        sql_cmd = f'drop table if exists {self.table_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res, f'执行失败:{text}')

        text = '----恢复pg_hba.conf文件;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'cp {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test {macro.DB_INSTANCE_PATH}/pg_hba.conf;' \
              f'gs_om -t restart'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)
        time.sleep(30)
        cmd = f'source {macro.DB_ENV_PATH};gs_om -t status --detail'
        status_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(f'数据库集群状态:\n{status_res}')
        cmd = f'source {macro.DB_ENV_PATH};rm -f {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test'
        self.pri_sh.node.sh(cmd).result()
        self.log.info('pg_hba.conf备份文件已清理')

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
